﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace BookClubIt.Data.Commands
{
    public class BookClubCommandList
    {
        public static SqlCommand SaveBookClub
        {
            get
            {
   
                SqlCommand command = new SqlCommand();
                command.Parameters.Add("@Id", SqlDbType.Int);
                command.Parameters.Add("@Name", SqlDbType.VarChar, 100);
                command.Parameters.Add("@ClubType", SqlDbType.Int);
                command.Parameters.Add("@GenreType", SqlDbType.Int);
                command.Parameters.Add("@CreatedDate", SqlDbType.DateTime);
                command.Parameters.Add("@Description", SqlDbType.VarChar, 300);
                command.Parameters.Add("@Rules", SqlDbType.VarChar, 300);
                command.Parameters.Add("@Website", SqlDbType.VarChar, 1000);
                command.Parameters.Add("@AdultGroup", SqlDbType.Int);
                command.Parameters.Add("@NonLeaderAddEvents", SqlDbType.Int);
                command.Parameters.Add("@NonLeaderAddPolls", SqlDbType.Int);
                command.Parameters.Add("@NonLeaderAddTopics", SqlDbType.Int);
                command.Parameters.Add("@GroupMeetsRealLife", SqlDbType.Int);
                command.Parameters.Add("@AvatarURL", SqlDbType.VarChar, 1000);
                command.Parameters["@Id"].Direction = ParameterDirection.InputOutput;
                command.CommandType = CommandType.Text;
                command.CommandText = @"

                if(not exists(select 1 from Book_Club where id = @Id))
                begin
                    INSERT INTO [Book_Club]
                           ([Name]
                           ,[Club_Type_Id]
                           ,[Genre_Id]
                           ,[Created_Date]
                           ,[Club_Description]
                           ,[Rules]
                            ,[Website]
                            ,[AdultGroup]
                            ,[NonLeaderAddEvents]
                            ,[NonLeaderAddPolls]
                            ,[NonLeaderAddTopics]
                            ,[GroupMeetsRealLife]
                            ,[AvatarURL])
                     VALUES
                           (@Name
                           ,@ClubType
                           ,@GenreType
                           ,@CreatedDate
                           ,@Description
                           ,@Rules
                           ,@Website
                            ,@AdultGroup
                            ,@NonLeaderAddEvents
                            ,@NonLeaderAddPolls
                            ,@NonLeaderAddTopics
                            ,@GroupMeetsRealLife
                            ,@AvatarURL)

                    set @Id = scope_identity()
                end
                else
                begin
                    UPDATE [Book_Club]
                       SET [Name] = @Name
                          ,[Club_Type_Id]= @ClubType
                           ,[Genre_Id]= @GenreType
                           ,[Created_Date]= @CreatedDate
                           ,[Club_Description]= @Description
                           ,[Rules]= @Rules
                            ,[Website]= @Website
                            ,[AdultGroup]= @AdultGroup
                            ,[NonLeaderAddEvents]= @NonLeaderAddEvents
                            ,[NonLeaderAddPolls]= @NonLeaderAddPolls
                            ,[NonLeaderAddTopics]= @NonLeaderAddTopics
                            ,[GroupMeetsRealLife]= @GroupMeetsRealLife
                            ,[AvatarURL]= @AvatarURL
                     WHERE id = @Id
                end
                ";
                return command;
            }
        }

        public static SqlCommand GetBookClubType
        {
            get
            {
                SqlCommand command = new SqlCommand();
                command.Parameters.Add("@Id", SqlDbType.Int);
                command.CommandType = CommandType.Text;
                command.CommandText = @"
                       Select [Club_Type_Name]
                       FROM [Club_Type]
                       WHERE id = @Id";
                return command;
            }
        }
   
        public static SqlCommand UpdateCurrentBook
            {
                get
                {
                    SqlCommand command = new SqlCommand();
                    command.Parameters.Add("@Bookclub_Id", SqlDbType.Int);
                    command.Parameters.Add("@Book_Id", SqlDbType.Int);
                    command.CommandType = CommandType.Text;
                    command.CommandText = @"
                            UPDATE Book_In_Club
                            SET Is_Current = 0
                            WHERE Book_Club_Id = @Bookclub_Id
                            AND Is_Current = 1

                            UPDATE Book_In_Club
                            SET Is_Current = 1
                            WHERE Book_Club_Id = @Bookclub_Id
                            AND Book_Id = @Book_Id";
                    return command;
                }
            }

        public static SqlCommand ApprovePendingMember
        {
            get
            {
                SqlCommand command = new SqlCommand();
                command.Parameters.Add("@Bookclub_Id", SqlDbType.Int);
                command.Parameters.Add("@User_Id", SqlDbType.Int);
                command.CommandType = CommandType.Text;
                command.CommandText = @"
                            UPDATE Member_Book_Club
                            SET Is_Approved = 1
                            WHERE Book_Club_Id = @Bookclub_Id
                            AND Member_Id = @User_Id
                            ";
                return command;
            }
        }

        public static SqlCommand DenyPendingMember
        {
            get
            {
                SqlCommand command = new SqlCommand();
                command.Parameters.Add("@Bookclub_Id", SqlDbType.Int);
                command.Parameters.Add("@User_Id", SqlDbType.Int);
                command.CommandType = CommandType.Text;
                command.CommandText = @"
                            DELETE FROM  Member_Book_Club
                            WHERE Book_Club_Id = @Bookclub_Id
                            AND Member_Id = @User_Id
                            ";
                return command;
            }
        }

        public static SqlCommand SendBookClubMessage
        {
            get
            {
                SqlCommand command = new SqlCommand();
                command.Parameters.Add("@Bookclub_Id", SqlDbType.Int);
                command.Parameters.Add("@Sender_User_Id", SqlDbType.Int);
                command.Parameters.Add("@Reciever_User_Id", SqlDbType.Int);
                command.Parameters.Add("@Message", SqlDbType.VarChar, 9999);
                command.Parameters.Add("@Sent_Date", SqlDbType.DateTime);
                command.CommandType = CommandType.Text;
                command.CommandText = @"
                             declare @Id int
        INSERT INTO [dbo].[Book_Club_Message]
                           ([Book_Club_Id]
                           ,[Sender_User_Id]
                           ,[Reciever_User_Id]
                            ,[Message]
                            ,[Sent_Date])
                     VALUES
                           (@Bookclub_Id
                           ,@Sender_User_Id
                           ,@Reciever_User_Id
                            ,@Message
                            ,@Sent_Date)

        SET @Id = SCOPE_IDENTITY()";
                return command;
            }
        }

        public static SqlCommand DeleteBookClub
        {
            get
            {
                SqlCommand command = new SqlCommand();
                command.Parameters.Add("@Bookclub_Id", SqlDbType.Int);
                command.CommandType = CommandType.Text;
                command.CommandText = @"
                            DELETE FROM Book_Club WHERE Id = @Bookclub_Id
                            DELETE FROM Member_Book_Club WHERE Book_Club_Id = @Bookclub_Id
                            DELETE FROM Leader_Book_Club WHERE Book_Club_Id = @Bookclub_Id
                            DELETE FROM Book_In_Club WHERE Book_Club_Id = @Bookclub_Id
                            DELETE FROM Activity WHERE Book_Club_Id = @Bookclub_Id
                            DELETE FROM Meeting WHERE Book_Club_Id = @Bookclub_Id
                            DELETE FROM Topic WHERE Book_Club_Id = @Bookclub_Id
                            DELETE  td
                            FROM Topic_Discussion AS td
                            LEFT JOIN
                            Topic AS t 
                            ON t.Id = td.Topic_Id
                            WHERE t.Book_Club_Id = @Bookclub_Id
                            ";
                return command;
            }

        }
        public static SqlCommand AddBookToClubShelf
            {
                get
                {
                    SqlCommand command = new SqlCommand();
                    command.Parameters.Add("@Bookclub_Id", SqlDbType.Int);
                    command.Parameters.Add("@Book_Id", SqlDbType.Int);
                    command.CommandType = CommandType.Text;
                    command.CommandText = @"
                declare @Id int
             if(not exists(select 1 from Book_In_Club where Book_Id = @Book_Id AND Book_club_Id = @Bookclub_Id))
                begin
                       INSERT INTO [dbo].[Book_In_Club]
                           ([Book_Club_Id]
                           ,[Book_Id]
                           ,[Is_Current])
                     VALUES
                           (@Bookclub_Id
                           ,@Book_Id
                           ,0)
                        
                set @Id = scope_identity()
                end
                           ";
                    return command;
                }
            }
        public static SqlCommand RemoveBookFromClubShelf
            {
                get
                {
                    SqlCommand command = new SqlCommand();
                    command.Parameters.Add("@Bookclub_Id", SqlDbType.Int);
                    command.Parameters.Add("@Book_Id", SqlDbType.Int);
                    command.CommandType = CommandType.Text;
                    command.CommandText = @"DELETE FROM Book_In_Club WHERE Book_Club_Id = @Bookclub_Id AND Book_Id = @Book_Id";
                    return command;
                }
            }

        public static SqlCommand DeleteTopicComment
        {
            get
            {
                SqlCommand command = new SqlCommand();
                command.Parameters.Add("@Topic_Id", SqlDbType.Int);
                command.CommandType = CommandType.Text;
                command.CommandText = @"DELETE FROM Topic_Discussion where Id = @Topic_Id";
                return command;
            }
        }

        public static SqlCommand CreateTopicComment
        {
            get
            {
                SqlCommand command = new SqlCommand();
                command.Parameters.Add("@Topic_Id", SqlDbType.Int);
                command.Parameters.Add("@User_Id", SqlDbType.Int);
                command.Parameters.Add("@Topic_Comment", SqlDbType.VarChar, 9999999);
                command.Parameters.Add("@Comment_Date", SqlDbType.DateTime);
                command.CommandType = CommandType.Text;
                command.CommandText = @"
                declare @Id int
        INSERT INTO [dbo].[Topic_Discussion]
                           ([Topic_Id]
                           ,[User_Id]
                           ,[Topic_Comment]
                            ,[Comment_Date])
                     VALUES
                           (@Topic_Id
                           ,@User_Id
                           ,@Topic_Comment
                            ,@Comment_Date)

        SET @Id = SCOPE_IDENTITY()";
                return command;
            }
        }

        public static SqlCommand CreateTopic
        {
            get
            {
                SqlCommand command = new SqlCommand();
                command.Parameters.Add("@Book_Club_Id", SqlDbType.Int);
                command.Parameters.Add("@Book_Id", SqlDbType.Int);
                command.Parameters.Add("@Topic_Name", SqlDbType.VarChar, 999);
                command.Parameters.Add("@ASIN", SqlDbType.VarChar, 100);
                command.Parameters.Add("@Comment_Date", SqlDbType.DateTime);
                command.CommandType = CommandType.Text;
                command.CommandText = @"
                declare @Id int
        INSERT INTO [dbo].[Topic]
                           ([Book_Club_Id]
                           ,[Book_Id]
                           ,[Topic_Name]
                            ,[ASIN]
                            ,[Comment_Date])
                     VALUES
                           (@Book_Club_Id
                           ,@Book_Id
                           ,@Topic_Name
                            ,@ASIN
                            ,@Comment_Date)

        SET @Id = SCOPE_IDENTITY()";
                return command;
            }
        }

        public static SqlCommand AddBookClubActivity
        {
            get
            {
                SqlCommand command = new SqlCommand();
                command.Parameters.Add("@Id", SqlDbType.Int);
                command.Parameters.Add("@User_Id", SqlDbType.Int);
                command.Parameters.Add("@Bookclub_Id", SqlDbType.Int);
                command.Parameters.Add("@Type", SqlDbType.VarChar, 100);
                command.Parameters.Add("@Data", SqlDbType.VarChar, 99999);
                command.Parameters.Add("@Time", SqlDbType.DateTime);
                command.Parameters["@Id"].Direction = ParameterDirection.InputOutput;
                command.CommandType = CommandType.Text;
                command.CommandText = @"
                if(not exists(select 1 from Activity where id = @Id))
                begin
                       INSERT INTO [dbo].[Activity]
                           ([User_Id]
                           ,[Book_Club_Id]
                           ,[Type]
                           ,[Data]
                           ,[Time])
                     VALUES
                           (@User_Id
                           ,@Bookclub_Id
                           ,@Type
                           ,@Data
                           ,@Time)
                        
                set @Id = scope_identity()
                end
                ";
                return command;
            }
        }

        public static SqlCommand SaveMeeting
        {
            get
            {
                SqlCommand command = new SqlCommand();
                command.Parameters.Add("@Id", SqlDbType.Int);
                command.Parameters.Add("@Book_Club_Id", SqlDbType.Int);
                command.Parameters.Add("@User_Id", SqlDbType.Int);
                command.Parameters.Add("@Title", SqlDbType.VarChar, 100);
                command.Parameters.Add("@Street", SqlDbType.VarChar, 100);
                command.Parameters.Add("@City", SqlDbType.VarChar, 100);
                command.Parameters.Add("@State", SqlDbType.VarChar, 100);
                command.Parameters.Add("@Zipcode", SqlDbType.VarChar, 100);
                command.Parameters.Add("@Created", SqlDbType.DateTime);
                command.Parameters["@Id"].Direction = ParameterDirection.InputOutput;
                command.CommandType = CommandType.Text;
                command.CommandText = @"
             if(not exists(select 1 from Meeting where Id = @Id))
                begin
                       INSERT INTO [dbo].[Meeting]
                           ([Book_Club_Id]
                           ,[User_Id]
                           ,[Title]
                            ,[Street]
                           ,[City]
                            ,[State]
                           ,[Zipcode]
                            ,[Created])
                     VALUES
                           (@Book_Club_Id
                           ,@User_Id
                           ,@Title
                            ,@Street
                            ,@City
                            ,@State
                            ,@Zipcode
                            ,@Created)
                        
                set @Id = scope_identity()
                end";
                return command;
            }
        }

        }

}
